Attribute VB_Name = "Inclusion_List_RT_Update"
'QE RETENTION TIME UPDATE
'This macro updates the inclusion list retention times using retention
'time data from an Excel retention time report created by TraceFinder.

'Option Private Module

Sub RT_Update()


'VARIABLES


'ARRAYS

Dim Updated_RT() As Variant
Dim Not_updated() As Variant

'BOOLEAN

Dim Bad_lookup As Boolean
Dim Multipeak As Boolean
Dim Excluded As Boolean



'INTEGERS

Dim Found_counter As Integer
Dim Not_found_counter As Integer
Dim FirstDataRow As Integer
Dim SearchRow As Integer
Dim SearchCol As Integer
Dim DataRow As Integer


'REFERENCES

Dim ReportBook As Workbook
Dim ReportSheet As Worksheet
Dim SearchCell As Range
Dim SearchRange As Range
Dim DataBook As Workbook
Dim DataSheet As Worksheet
Dim NameCell As Range


'STRINGS

Dim Compound_list As String
Dim File_date As String
Dim Report_name As String
Dim File_name As String
Dim LookupName As String
Dim Existing_RT As String




'INITIAL SETTINGS

Application.ScreenUpdating = False

FirstDataRow = 8  '<--------------- FIRST ROW IN SUMMARY SCAN EVENT TABLE

Set DataBook = ThisWorkbook
Set DataSheet = DataBook.Worksheets(1)
Set DataTable = DataSheet.ListObjects("Table4")


'OPEN RT_REPORT FILE

ChDrive "C"
'ChDir ("C:\TraceFinderData\")

File_name = Application.GetOpenFilename("Excel files (*.xlsx), *.xlsx", Title:="Please select a Retention Time Report")
If File_name = "False" Then Exit Sub  'EXIT MACRO WITHOUT SAVING IF USER SELECTS CANCEL



'OPEN RT_REPORT AND SET REFERENCES

Set ReportBook = Workbooks.Open(File_name)
Set ReportSheet = ReportBook.Worksheets(1)


'DISPLAY FILENAME AND ACQUISITION DATE/TIME TO USER
'FIRST FIND FILENAME AND ACQUISITION DATE/TIME FIELDS

Set SearchRange = ReportSheet.Range("A1:Z10")

Set SearchCell = SearchRange.Find("Data File:")
If Not SearchCell Is Nothing Then
SearchCol = SearchCell.Column
SearchRow = SearchCell.Row
Report_name = ReportSheet.Cells(SearchRow, SearchCol + 1)
End If  'END IF FOR SEARCH STRING FOUND

Set SearchCell = SearchRange.Find("Acquisition Date:")
If Not SearchCell Is Nothing Then
SearchCol = SearchCell.Column
SearchRow = SearchCell.Row
Report_date = ReportSheet.Cells(SearchRow, SearchCol + 1)
End If  'END IF FOR SEARCH STRING FOUND

MsgBox "File Name:   " & Report_name & vbLf & "Acquired:    " & Report_date, vbInformation, "EZ-METHOD MANAGER"



'DETERMINE RANGE OF RT VALUES

ReportSheet.Activate
ReportSheet.Cells(5, 1).Select
Set DataRange = ActiveCell.CurrentRegion  'DETERMINE NUMBER OF COMPOUND DATA ROWS TO PROCESS

RowCount = DataRange.Rows.Count



'SET NAMED RANGE FOR RT_TABLE

DataBook.Names.Add Name:="RT_table", _
    RefersTo:=ReportSheet.Range(Cells(5, 1), Cells(RowCount + 4, 7))
    


'SWITCH TO COMPOUND DATA WORKBOOK

DataBook.Activate



'LOOP THROUGH SUMMARY SHEET COMPOUNDS AND UPDATE RT IF AVAILABLE


For Each NameCell In DataTable.ListColumns(1).DataBodyRange.SpecialCells(xlCellTypeVisible)
DataRow = NameCell.Row - 6


'SET Bad_lookup FLAG AS FALSE FOR NEXT COMPOUND

Bad_lookup = False

LookupName = NameCell


'SET EXCLUDED COMPOUNDS - LARGE DIFFERENCE IN RT FOR MULTI PEAK COMPOUNDS REQUIRES MANUAL RT ADJUSTMENT

'If LookupName Like "*Clethodim*" Or LookupName Like "*Sethoxydim*" Or _
'Len(DataSheet.Cells(DataRow, 18)) > 0 Then Excluded = True Else Excluded = False



'BACKUP EXISTING RT IN CASE COMPOUND NOT FOUND (CLEAR ERRORS IN RT CELL FIRST)

If IsError(DataTable.ListColumns("RT").Range(DataRow)) Then DataTable.ListColumns("RT").Range(DataRow) = "N/A"

Existing_RT = DataTable.ListColumns("RT").Range(DataRow)



'VLOOKUP RT FROM RT_Table NAMED RANGE

DataTable.ListColumns("RT").Range(DataRow) = "=VLOOKUP(""" & LookupName & """,RT_table,7,False)"



'GET AVERAGE RT FOR MULTIPEAK COMPOUNDS - EXCLUDE COMPOUNDS WITH LARGE RT DIFFERENCE

If Not IsError(DataTable.ListColumns("RT").Range(DataRow)) Then
If InStr(DataTable.ListColumns("RT").Range(DataRow), ",") > 0 Then Multipeak = True Else Multipeak = False
If Multipeak And Not Excluded Then _
DataTable.ListColumns("RT").Range(DataRow) = "=VLOOKUP(""" & LookupName & """,RT_table,6,False)"
End If  'END IF FOR NO ERROR IN RT CELL




'IF ERROR OCCURS (LOOKUP NAME NOT FOUND) OR IF THIS IS A MULTIPEAK COMPOUND (COMMA PRESENT),
'THEN RESTORE ORIGINAL RT TO VALUE TO CELL

If IsError(DataTable.ListColumns("RT").Range(DataRow)) Then
DataTable.ListColumns("RT").Range(DataRow) = Existing_RT
Bad_lookup = True
ElseIf InStr(DataTable.ListColumns("RT").Range(DataRow), ",") > 0 Or DataTable.ListColumns("RT").Range(DataRow) = 0 Then
DataTable.ListColumns("RT").Range(DataRow) = Existing_RT
Bad_lookup = True
End If


'IF COMPOUND NOT FOUND, RESTORE ORIGINAL RT TO CELL

If DataTable.ListColumns("RT").Range(DataRow) = "N/F" Then
DataTable.ListColumns("RT").Range(DataRow) = Existing_RT
Bad_lookup = True
End If

'ADD FOUND AND NOT FOUND COMPOUNDS TO ARRAYS TO NOTIFY USER - EXCLUDE COMPOUNDS WITH LARGE RT DIFFERENCE (CLETHODIM/SETHOXYDIM)

If Bad_lookup = True And Not Excluded Then
ReDim Preserve Not_updated(1, Not_found_counter) 'ADD COMPOUND TO ARRAY
Not_updated(0, Not_found_counter) = DataTable.ListColumns("Compound").Range(DataRow)
Not_updated(1, Not_found_counter) = DataTable.ListColumns("Standard Mix").Range(DataRow)
Not_found_counter = Not_found_counter + 1
End If

If Bad_lookup = False And Not Excluded Then
ReDim Preserve Updated_RT(1, Found_counter) 'ADD COMPOUND TO ARRAY
Updated_RT(0, Found_counter) = DataTable.ListColumns("Compound").Range(DataRow)
Updated_RT(1, Found_counter) = DataTable.ListColumns("Standard Mix").Range(DataRow)
Found_counter = Found_counter + 1
End If


'CONVERT CELL CONTENTS TO VALUE SO IT IS NOT LINKED

DataTable.ListColumns("RT").Range(DataRow) = DataTable.ListColumns("RT").Range(DataRow).Value

Next NameCell



'CLEAR NAMED RT_TABLE RANGE

DataBook.Names("RT_table").Delete



'CLOSE RT_REPORT

ReportBook.Close (False)

DataSheet.Cells(1, 1).Select



'SET LAST UPDATED DATE AND TIME

DataSheet.Range("A5") = "RT Update " & "  " & Date & "  " & Time



'DISPLAY LIST OF COMPOUNDS THAT WERE UPDATED

If Found_counter = 0 Then

MsgBox "No compounds updated - check export file.", vbCritical, "INCLUSION LIST MANAGER"

ElseIf Found_counter <> 0 Then


ReDim Preserve Updated_RT(1, Found_counter)
Updated_RT(0, Found_counter) = ""
Found_counter = Found_counter + 1

ReDim Preserve Updated_RT(1, Found_counter)
Updated_RT(0, Found_counter) = "Total Updated = " & Found_counter - 1
Found_counter = Found_counter + 1

With UserForm1
   .ListBox1.List = Application.Transpose(Updated_RT)
   .Show
End With
End If

DoEvents



'DISPLAY LIST OF COMPOUNDS THAT WERE NOT UPDATED

If Not_found_counter = 0 Then

ElseIf Not_found_counter <> 0 Then

ReDim Preserve Not_updated(1, Not_found_counter)
Not_updated(0, Not_found_counter) = ""
Not_found_counter = Not_found_counter + 1

ReDim Preserve Not_updated(1, Not_found_counter)
Not_updated(0, Not_found_counter) = "Total NOT Updated = " & Not_found_counter - 1
Not_found_counter = Not_found_counter + 1

With UserForm2
   .ListBox1.List = Application.Transpose(Not_updated)
   .Show
End With
End If

DoEvents


End Sub

